package com.diqye.db;

import static com.diqye.db.exception.XAssert.asertnotnull;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import com.diqye.db.dialect.IDialect;
import com.diqye.db.event.TableEvent;
import com.diqye.db.event.TableEventSave;
import com.diqye.db.event.TableEventUpdate;
import com.diqye.db.exception.XAssert;
import com.diqye.db.exception.XException;
import com.diqye.db.manage.XManage;
import com.diqye.db.model.XModel;
import com.diqye.db.sql.SqlLoader;
import com.diqye.log.Logger;
import com.diqye.templet.util.Sqlpara;
import com.diqye.templet.util.Strings;

public class XDB {

	private final Logger log = Logger.getLogger(XDB.class);
	public DataSource datasource;
	public Connection connection;
	public SqlLoader loader;
	private boolean transactionOpened=false;
	public XDB(DataSource datasource){
		XAssert.asertnotnull(datasource, "datasource不能为null");
		this.datasource = datasource;
		this.loader=XConf.defaultSqlloder;
	}
	
	public XDB(){
		this(XConf.defaultDataSource);
	}
	
	public Connection getConn(){
		try {
			if(transactionOpened){
				return connection;
			}
			
			return datasource.getConnection();
		} catch (SQLException e) {
			throw new XException("获取数据库连接异常",e);
		}
	}
	/**
	 * 只适用于表中只有一个主建的情况
	 * @param tinfo
	 * @param oid
	 * @return
	 */
	public  XModel findById(Tinfo tinfo,Object oid){
		String sql = XManage.getDialect(tinfo.getClass()).findbyidsql(tinfo);
		XModel r= findFirst(sql, new XModel().s(tinfo.getPrimaryKey().get(0),oid));
		if(r!=null)r.setTinfo(tinfo);
		return r;
	}
	
	public  XModel findById(Class<? extends Tinfo> tinfoclass,Object oid){
		return findById(XManage.getTinfo(tinfoclass),oid);
	}
	
	public  XModel findById(XModel model){
		Tinfo tinfo = model.getTinfo();
		if(tinfo == null){
			throw new XException("tinfo 不能为空");
		}
		String sql = XManage.getDialect(tinfo.getClass()).findbyidsql(tinfo);
		XModel r= findFirst(sql, model);
		if(r!=null)r.setTinfo(tinfo);
		return r;
	}
	public  Object findObject(String sql,Object...paras){
		XModel model= findFirst(sql,paras);
		if(model==null)return model;
		Iterator<String> iter = model.keySet().iterator();
		if(iter.hasNext()){
			return model.get(iter.next());
		}else{
			return null;
		}
	}
	
	@SuppressWarnings("rawtypes")
	public  Object findObject(String sql,Map para){
		XModel model= findFirst(sql,para);
		if(model==null)return model;
		Iterator<String> iter = model.keySet().iterator();
		if(iter.hasNext()){
			return model.get(iter.next());
		}else{
			return null;
		}
	}
	public  String findString( String sql,Object...paras){
		Object o = findObject(sql,paras);
		if(o instanceof String){
			return (String)o;
		}
		if(o == null)return null;
		return String.valueOf(o);
	}
	@SuppressWarnings("rawtypes")
	public  String findString( String sql,Map para){
		Object o = findObject(sql,para);
		if(o instanceof String){
			return (String)o;
		}
		if(o == null)return null;
		return String.valueOf(o);
	}
	public  Long findLong( String sql,Object...paras){
		Object o = findObject(sql,paras);
		if(o instanceof Number){
			return ((Number)o).longValue();
		}
		if(o == null)return null;
		return Long.valueOf(o.toString());
	}
	@SuppressWarnings("rawtypes")
	public  Long findLong( String sql,Map para){
		Object o = findObject(sql,para);
		if(o instanceof Number){
			return ((Number)o).longValue();
		}
		if(o == null)return null;
		return Long.valueOf(o.toString());
	}
	
	public  Double findDouble( String sql,Object...paras){
		Object o = findObject(sql,paras);
		if(o instanceof Number){
			return ((Number)o).doubleValue();
		}
		if(o == null)return null;
		return Double.valueOf(o.toString());
	}
	@SuppressWarnings("rawtypes")
	public  Double findDouble( String sql,Map para){
		Object o = findObject(sql,para);
		if(o instanceof Number){
			return ((Number)o).doubleValue();
		}
		if(o == null)return null;
		return Double.valueOf(o.toString());
	}
	public  XModel findFirst(String sql,Object...paras){
		List<XModel> modellist = find(sql,paras);
		if(modellist.size()>0){
			return modellist.get(0);
		}
		return null;
	}
	@SuppressWarnings("rawtypes")
	public  XModel findFirst(String sql,Map paras){
		List<XModel> modellist = find(sql,paras);
		if(modellist.size()>0){
			return modellist.get(0);
		}
		return null;
	}
	
	public  List<XModel> find(String sql,Object...paras){
		if(loader !=null&&sql.startsWith(":")){
			sql=loader.getSql(sql.replaceFirst(":", ""));
		}
		Sqlpara para = Strings.formatsql(Strings.formatfill(sql, paras), paras);
		return jdbcfind(para.sql,para.paras);
	}
	@SuppressWarnings("rawtypes")
	public  List<XModel> find(String sql, Map paras){
		if(loader !=null&&sql.startsWith(":")){
			sql=loader.getSql(sql.replaceFirst(":", ""));
		}
		
		Sqlpara para = Strings.formatsql(Strings.formatfill(sql, paras), paras);
		return jdbcfind(para.sql,para.paras);
	}
	
	@SuppressWarnings("rawtypes")
	public  List<XModel> jdbcfind( String sql,List paras){
		log.debug(Strings.format("\n----------\n{0}\n====paras====\n{1}\n----------\n", sql,Strings.join(paras, "+")));
		List<XModel> querylist = new ArrayList<XModel>();
		PreparedStatement  pstmt = null;
		ResultSet rs = null;
		Connection conn = getConn();
		try{
			pstmt = conn.prepareStatement(sql);
			if(paras != null){
				for (int i = 0; i < paras.size(); i++) {
					pstmt.setObject(i+1, paras.get(i));
				}
			}
			rs = pstmt.executeQuery();
			ResultSetMetaData meta = rs.getMetaData();
			while(rs.next()){
				XModel model = new XModel();
				for(int i=0;i<meta.getColumnCount();i++){
					model.set(meta.getColumnLabel(i+1), rs.getObject(meta.getColumnLabel(i+1)));
				}
				querylist.add(model);
			}
		}catch (SQLException e) {
			throw new XException("执行SQL语句异常",e);
		}finally{
			closeConn(conn);
			closeStatmentResultSet(pstmt, rs);
		}
		return querylist;
	}
	public  int update(String sql,Object...paras){
		if(loader !=null&&sql.startsWith(":")){
			sql=loader.getSql(sql.replaceFirst(":", ""));
		}
		Sqlpara para = Strings.formatsql(Strings.formatfill(sql, paras), paras);
		return jdbcupdate(para.sql,para.paras);
	}
	
	@SuppressWarnings("rawtypes")
	public int update(String sql,Map param){
		if(loader !=null&&sql.startsWith(":")){
			sql=loader.getSql(sql.replaceFirst(":", ""));
		}
		Sqlpara para = Strings.formatsql(Strings.formatfill(sql, param), param);
		return jdbcupdate(para.sql,para.paras);
	}

	
	@SuppressWarnings("rawtypes")
	public  int jdbcupdate(String sql,List paras){
		log.debug(Strings.format("\n----------\n{0}\n====paras====\n{1}\n----------\n", sql,Strings.join(paras, "+")));
		PreparedStatement  pstmt = null;
		int r = 0;
		Connection conn = getConn();
		try{
			pstmt = conn.prepareStatement(sql);
			if(paras != null){
				for (int i = 0; i < paras.size(); i++) {
					pstmt.setObject(i+1, paras.get(i));
				}
			}
			r = pstmt.executeUpdate();
		}catch (SQLException e) {
			throw new XException("执行SQL语句异常",e);
		}finally{
			closeConn(conn);
			closeStatmentResultSet(pstmt, null);
		}
		
		return r;
	}
	
	public void closeConn(Connection conn){
		if(!transactionOpened){
			 try {
					conn.close();
				} catch (SQLException e) {
					throw new XException("关闭连接异常",e);
				}
		}
	}
	
	 public  void closeStatmentResultSet(Statement stmt, ResultSet rs){
		 if(stmt != null ){
			 try {
				stmt.close();
			} catch (SQLException e) {
				throw new XException("关闭连接异常",e);
			}
		 }
		 
		 if(rs != null ){
			 try {
				rs.close();
			} catch (SQLException e) {
				throw new XException("关闭连接异常",e);
			}
		 }
	 }
	 public void beginTransaction(){
		 try {
			 	
			 	connection=getConn();
			 	transactionOpened=true;
			 	connection.setAutoCommit(false);
			} catch (SQLException e) {
				new XException("设置jdbc事务失败",e);
			}
			//conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); //默认级别的事务
	}
	 
	public void commit(){
		try {
		 	
			connection.setAutoCommit(true);
		 	connection.commit();
		 	transactionOpened=false;
		 	closeConn(connection);
		} catch (SQLException e) {
			new XException("设置jdbc事务失败",e);
		}
	}
	
	public void rollback(){
		 try {
			 connection.rollback();
			 connection.setAutoCommit(true);
			 transactionOpened=false;
			 closeConn(connection);
		} catch (SQLException e) {
			new XException("设置jdbc事务失败",e);
		}
	}
	
	@SuppressWarnings("rawtypes")
	public  List<String> jdbcFields(String sql,List paras){
		log.debug("sql->"+sql);
		log.debug("paras->"+Strings.join(paras, ","));
		List<String> rlist = new ArrayList<String>();
		PreparedStatement  pstmt = null;
		ResultSet rs = null;
		Connection conn=getConn();
		try{
			pstmt = conn.prepareStatement(sql);
			if(paras != null){
				for (int i = 0; i < paras.size(); i++) {
					pstmt.setObject(i+1, paras.get(i));
				}
			}
			rs = pstmt.executeQuery();
			ResultSetMetaData meta = rs.getMetaData();
			for(int i=0;i<meta.getColumnCount();i++){
				rlist.add(meta.getColumnName(i+1));
			}
			
		}catch (SQLException e) {
			throw new XException("执行SQL语句异常",e);
		}finally{
			closeConn(conn);
			closeStatmentResultSet(pstmt, rs);
		}
		return rlist;
	}
	
	
	public boolean save(XModel model){
		eachEventRunBefore(model,"save");
		IDialect dialect = XManage.getDialect(model.getTinfo().getClass());
		String sql = dialect.savesql(model);
		int r = update(sql, model);
		eachEventRunAfter(model,"save");
		return r>0;
	}
	/**
	 * update by id
	 * @return
	 */
	public boolean  update(XModel model){
		eachEventRunBefore(model,"update");
		asertnotnull(model.getTinfo(),"tinfo为null");
		for(String key : model.getTinfo().getPrimaryKey()){
			asertnotnull(model.get(key), "表{0}中{1}不允许为null",model.getTinfo().getTablename(),key);
		}
		IDialect dialect = XManage.getDialect(model.getTinfo().getClass());
		String sql = dialect.updatesql(model);
		int r= update(sql, model);
		eachEventRunAfter(model,"update");
		return r>0;
	}
	public boolean saveOrUpdate(XModel model){
		XModel model1 = findById(model);
		if(model1 == null){
			return save(model);
		}else{
			return update(model);
		}
	}
	public boolean delete(XModel model){
		asertnotnull(model.getTinfo(),"tinfo为null");
		for(String key : model.getTinfo().getPrimaryKey()){
			asertnotnull(model.get(key), "表{0}中{1}不允许为null",model.getTinfo().getTablename(),key);
		}
		IDialect dialect = XManage.getDialect(model.getTinfo().getClass());
		String sql = dialect.deletesql(model.getTinfo());
		int r = update(sql, model);
		return r>0;
	}

	public  void eachEventRunBefore(XModel model,String ename){
		asertnotnull(model.getTinfo(),"tinfo为null");
		List<TableEvent> telist = XManage.getTableEvents(model.getTinfo().getClass());
		if(telist == null)return;
		for(TableEvent e : telist){
			if("save".equals(ename)){
				if(e instanceof TableEventSave){
					e.before(model);
				}
			}
			if("update".equals(ename)){
				if(e instanceof TableEventUpdate){
					e.before(model);
				}
			}
		}
	}
	public  void eachEventRunAfter(XModel model,String ename){
		asertnotnull(model.getTinfo(),"tinfo为null");
		List<TableEvent> telist = XManage.getTableEvents(model.getTinfo().getClass());
		if(telist == null)return;
		for(TableEvent e : telist){
			if("save".equals(ename)){
				if(e instanceof TableEventSave){
					e.after(model);
				}
			}
			if("update".equals(ename)){
				if(e instanceof TableEventUpdate){
					e.after(model);
				}
			}
		}
	}
	
}
